import pymysql
import json
from urllib import request
import pinyin.cedict

db = pymysql.connect( "10.2.5.100", "root", "Kc@123456", "helmet_operate_sit" )
cursor = db.cursor()


def update_data():
    get_data_sql = 'SELECT GROUP_CONCAT(id) as id ,group_concat(problem_type) as typeId ,group_concat(problem_description) as description   FROM kc_device_quality_fault_t WHERE device_id != \'{0}\' and device_id != \'{1}\'  GROUP BY device_id'.format(
        '', '/' )
    cursor.execute( get_data_sql )
    data_list = cursor.fetchall()

    for data in data_list:
        id_list = data[0].split( ',' )
        if len( (id_list) ) > 1:
            update_sql = 'UPDATE kc_device_quality_fault_t set problem_type =\'{0}\',problem_description =\'{1}\'  WHERE id = \'{2}\''.format(
                data[1],data[2],id_list[0] )
            print(update_sql)
            cursor.execute( update_sql )
            db.commit()
            i = 0
            for id in id_list:
                if i > 0:
                    delect_sql = 'delete FROM kc_device_quality_fault_t WHERE id = \'{0}\''.format( id_list[i] )
                    print(delect_sql)
                    cursor.execute( delect_sql )
                    db.commit()

                i = i + 1
                if i >= len( (id_list) ):
                    i = 0


def add_ops_user():
    get_ops_sql = 'SELECT customer_id ,ops_user FROM kc_device_quality_fault_t WHERE customer_id != \'{0}\' and ops_user != \'{1}\''.format(
        '','')
    cursor.execute( get_ops_sql )
    opsdata = cursor.fetchall()
    add_path = 'http://183.62.134.116:8803/helmetOperate/helmetOperated/addOpsUsers'
    header = {'Content-Type': 'application/json',
              'trustToken': '93978ffb50540258 b76230b654fa50d58a0bf380'}
    for ops in opsdata:
        data_json = {"customerIds": [ops[0]],
                     "opsUserId": [ops[1]]}
        request_request = request.Request( add_path, json.dumps( data_json ).encode( 'utf-8' ), header )
        request.urlopen( request_request )

if __name__ == '__main__':
    #add_ops_user()
    update_data()
